from demo003.mysqlUtil import MysqlUtil
from demo003.studentBean import StudentBean


class ManageStudent:
    def __init__(self):
        #连接数据库
        self.conn = MysqlUtil(host='127.0.0.1',port=3306,user='root',password='root',db='shop',charset='utf8')

    def manage(self):
        while True:
            #显示选项
            self.show()
            #键盘录入
            num = int(input('请输入有效数字：'))
            #判断
            if num == 1:
                #查询所有信息
                self.selectAll()

            elif num == 2:
                self.selectByName()

            elif num == 3:
                self.add()

            elif num == 4:
                self.update()
            elif num == 5:
                self.deleteByName()
            elif num == 6:
                print('退出系统')
                break

    def selectAll(self):
        # 查询所有信息
        sql = 'select * from student'
        reslut = self.conn.query(sql)
        if len(reslut) == 0:
            print('暂无数据')
        else:
            for i in reslut:
                stu = StudentBean(i[0],i[1],i[2])
                print(stu)

    def show(self):
        print('=============学生管理系统==============')
        print('1:查询所有信息')
        print('2:姓名查询信息')
        print('3:添加信息')
        print('4:修改信息')
        print('5:删除信息')
        print('6:退出系统')

    def selectByName(self):
        name = input('请输入查询姓名：')

        result = self.conn.query('select * from student where name= %s', name)
        if len(result) == 0:
            print('暂无数据')
        else:
            for r in result:
                s = StudentBean(r[0], r[1], r[2])
                print(s)

    def add(self):
        name = input('请输入姓名：')
        sex = input('请输入性别：')
        age = input('请输入年龄：')
        self.conn.update('insert into student values(%s,%s,%s)', (name, sex, age))

    def update(self):
        name = input('请输入姓名：')
        sex = input('请输入性别：')
        age = input('请输入年龄：')
        self.conn.update('update student set name=%s,sex=%s where age=%s',(name,sex,age))

    def deleteByName(self):

        name = input('请输入要删除的姓名:')
        self.conn.update('delete from student where name=%s', name)


if __name__ == '__main__':
    ma = ManageStudent()
    ma.manage()
    MysqlUtil.close()
